package Data;

import java.io.InputStream;
import java.io.Reader;
import java.math.BigDecimal;
import java.net.URL;
import java.rmi.RemoteException;
import java.sql.Array;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.Date;
import java.sql.NClob;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.Ref;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.RowId;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.SQLXML;
import java.sql.Time;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Calendar;

import Interface_Data.UserDataI;
import Util.DBUtil;
import Vo.AdminVo;
import Vo.DeanVo;
import Vo.DepTVo;
import Vo.StuVo;
import Vo.TeaVo;

public class UserData implements UserDataI {
	DBUtil dbUtil = new DBUtil();
	Connection con = null;

	public UserData() throws Exception {
		super();
	}

	public static void main(String[] args) {
		try {
			UserData u = new UserData();
			
			u.insertStu(new StuVo("1","1","1",1,"1",1,1,1,1));
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	public String getIdentity(String id) throws RemoteException {

		String identity = "";
		String sql = "select * from admindb where id=?";
		try {
			con = dbUtil.getDataCon();
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setString(1, id);
			ResultSet rs = pstmt.executeQuery();

			if (rs.next()) {
				identity = rs.getString("identity");
			}else{
				sql = "select * from deptdb where id=?";
				pstmt = con.prepareStatement(sql);
				pstmt.setString(1, id);
				rs = pstmt.executeQuery();
				if (rs.next()) {
					identity = rs.getString("identity");
				}else{
					sql = "select * from teadb where id=?";
					pstmt = con.prepareStatement(sql);
					pstmt.setString(1, id);
					rs = pstmt.executeQuery();
					if (rs.next()) {
						identity = rs.getString("identity");
					}else{
						sql = "select * from studb where id=?";
						pstmt = con.prepareStatement(sql);
						pstmt.setString(1, id);
						rs = pstmt.executeQuery();
						if (rs.next()) {
							identity = rs.getString("identity");
						}else{
							sql = "select * from deandb where id=?";
							pstmt = con.prepareStatement(sql);
							pstmt.setString(1, id);
							rs = pstmt.executeQuery();
							if (rs.next()) {
								identity = rs.getString("identity");
							}
						}
					}
				}
				
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				dbUtil.closeConnection(con);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return identity;
	}

	public String getPassword(String id, String identity)
			throws RemoteException {
		String password = null;
		try {
			con = dbUtil.getDataCon();
			if(identity.equals("depT")){
				identity = "dept";
			}
			String sql = "select * from " + identity + "db" + " where id=?";
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setString(1, id);
			ResultSet rs = pstmt.executeQuery();

			if (rs.next()) {
				password = rs.getString("password");
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				dbUtil.closeConnection(con);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return password;
	}

	public String getName(String id, String identity) throws RemoteException {
		String name = null;
		try {
			con = dbUtil.getDataCon();
			if(identity.equals("depT")){
				identity = "dept";
			}
			String sql = "select * from " + identity + "db where id=?";
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setString(1, id);
			ResultSet rs = pstmt.executeQuery();

			if (rs.next()) {
				name = rs.getString("name");
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				dbUtil.closeConnection(con);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return name;
	}

	public String getDep(String id, String identity) throws RemoteException {
		String dep = null;
		if(identity.equals("depT")){
			identity = "dept";
		}
		String sql = "select *from " + identity + "db where id=?";
		try {
			
			con = dbUtil.getDataCon();
			PreparedStatement pstmt = con.prepareStatement(sql);
            pstmt.setString(1, id);
			ResultSet rs = pstmt.executeQuery();

			if (rs.next()) {
				dep = rs.getString("dep");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				dbUtil.closeConnection(con);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return dep;
	}

	@Override
	public boolean changePassword(String id, String password,String identity) {
		if(identity.equals("depT")){
			identity ="dept";
		}
		String sql = "Update "+identity+"db set password=? where id =?";
		try {
            con = dbUtil.getDataCon();
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setString(1, password);
			pstmt.setString(2, id);
			if(pstmt.executeUpdate()== 1 ){
				return true;
			}
		} catch (Exception e) {
             e.printStackTrace();
             return true;
		}finally{
			try {
				dbUtil.closeConnection(con);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return false;
	}

	@Override
	public AdminVo returnAdminInfo(String id) throws RemoteException {
		AdminVo adminVo = new AdminVo();
		String sql = "Select * from admindb where id =?";
		try{
			con = dbUtil.getDataCon();
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setString(1, id);
			ResultSet rs = pstmt.executeQuery();
			if(rs.next()){
				adminVo.setId(rs.getString("id"));
				adminVo.setName(rs.getString("name"));
				adminVo.setIdentity(rs.getString("identity"));
				return adminVo;
			}else{
				return null;
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try {
				dbUtil.closeConnection(con);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return null;
	}

	@Override
	public DeanVo returnDeanInfo(String id) throws RemoteException {
		// TODO Auto-generated method stub
		DeanVo deanVo = new DeanVo();
		String sql = "Select * from deandb where id =?";
		try{
			con = dbUtil.getDataCon();
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setString(1, id);
			ResultSet rs = pstmt.executeQuery();
			if(rs.next()){
				deanVo.setId(rs.getString("id"));
				deanVo.setName(rs.getString("name"));
				deanVo.setIdentity(rs.getString("identity"));
				return deanVo;
			}else{
				return null;
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try {
				dbUtil.closeConnection(con);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return null;
	}

	@Override
	public DepTVo returnDepTInfo(String id) throws RemoteException {
		// TODO Auto-generated method stub
		DepTVo depTVo = new DepTVo();
		String sql = "Select * from deptdb where id =?";
		try{
			con = dbUtil.getDataCon();
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setString(1, id);
			ResultSet rs = pstmt.executeQuery();
			if(rs.next()){
				depTVo.setId(rs.getString("id"));
				depTVo.setName(rs.getString("name"));
				depTVo.setDep(rs.getString("dep"));
				depTVo.setIdentity(rs.getString("identity"));
				return depTVo;
			}else{
				return null;
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try {
				dbUtil.closeConnection(con);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return null;
	}

	@Override
	public TeaVo returnTeaInfo(String id) throws RemoteException {
		// TODO Auto-generated method stub
		TeaVo teaVo = new TeaVo();
		String sql = "Select * from teadb where id =?";
		try{
			con = dbUtil.getDataCon();
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setString(1, id);
			ResultSet rs = pstmt.executeQuery();
			if(rs.next()){
				teaVo.setId(rs.getString("id"));
				teaVo.setName(rs.getString("name"));
				teaVo.setDep(rs.getString("dep"));
				teaVo.setIdentity(rs.getString("identity"));
				return teaVo;
			}else{
				return null;
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try {
				dbUtil.closeConnection(con);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return null;
	}

	@Override
	public StuVo returnStuInfo(String id) throws RemoteException {
		// TODO Auto-generated method stub
		StuVo stuVo = new StuVo();
		String sql = "Select * from studb where id =?";
		try{
			con = dbUtil.getDataCon();
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setString(1, id);
			ResultSet rs = pstmt.executeQuery();
			if(rs.next()){
			    stuVo.setId(rs.getString("id"));
				stuVo.setName(rs.getString("name"));
				stuVo.setDep(rs.getString("dep"));
				stuVo.setIdentity(rs.getString("identity"));
				stuVo.setGrade(rs.getInt("grade"));
				stuVo.setModel1Score(rs.getInt("model1Score"));
				stuVo.setModel2Score(rs.getInt("model2Score"));
				stuVo.setModel3Score(rs.getInt("model3Score"));
				stuVo.setModel4Score(rs.getInt("model4Score"));
				return stuVo;
			}else{
				return null;
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try {
				dbUtil.closeConnection(con);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return null;
	}

	@Override
	public void insertStu(StuVo stuVo) throws RemoteException {
		// TODO Auto-generated method stub
		try{
			con = dbUtil.getDataCon();
			String sql = "insert into studb value(?,?,?,?,?,?,?,?,?,?)";
			PreparedStatement ptmt = con.prepareStatement(sql);
			ptmt.setString(1, stuVo.getId());
			ptmt.setString(2, stuVo.getId());
			ptmt.setString(3, stuVo.getName());
			ptmt.setString(4, stuVo.getDep());
			ptmt.setString(5, ""+stuVo.getGrade());
			ptmt.setString(6, "stu");
			ptmt.setString(7, ""+stuVo.getModel1Score());
			ptmt.setString(8, ""+stuVo.getModel2Score());
			ptmt.setString(9, ""+stuVo.getModel3Score());
			ptmt.setString(10, ""+stuVo.getModel4Score());
			ptmt.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try {
				dbUtil.closeConnection(con);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
	}

	@Override
	public void insertDepT(DepTVo depT) throws RemoteException {
		// TODO Auto-generated method stub
		try{
			con = dbUtil.getDataCon();
			String sql = "insert into deptdb value(?,?,?,?,?)";
			PreparedStatement ptmt = con.prepareStatement(sql);
			ptmt.setString(1, depT.getId());
			ptmt.setString(2, depT.getId());
			ptmt.setString(3, depT.getName());
			ptmt.setString(4, depT.getDep());
			ptmt.setString(5, "depT");
			
			ptmt.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try {
				dbUtil.closeConnection(con);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
	}

	@Override
	public void insertTea(TeaVo tea) throws RemoteException {
		// TODO Auto-generated method stub
		try{
			con = dbUtil.getDataCon();
			String sql = "insert into teadb value(?,?,?,?,?)";
			PreparedStatement ptmt = con.prepareStatement(sql);
			ptmt.setString(1, tea.getId());
			ptmt.setString(2, tea.getId());
			ptmt.setString(3, tea.getName());
			ptmt.setString(4, tea.getDep());
			ptmt.setString(5, "tea");
			
			ptmt.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try {
				dbUtil.closeConnection(con);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
		
	}

	@Override
	public ArrayList<StuVo> returnDepStudentList(String dep)
			throws RemoteException {
		// TODO Auto-generated method stub
		
		String sql = "Select * from studb where dep =?";
		try{
			con = dbUtil.getDataCon();
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setString(1, dep);
			ResultSet rs = pstmt.executeQuery();
			ArrayList<StuVo> stuList = new ArrayList<StuVo>();
			while(rs.next()){
				StuVo stuVo = new StuVo();
			    stuVo.setId(rs.getString("id"));
				stuVo.setName(rs.getString("name"));
				stuVo.setDep(rs.getString("dep"));
				stuVo.setIdentity(rs.getString("identity"));
				stuVo.setGrade(rs.getInt("grade"));
				stuVo.setModel1Score(rs.getInt("model1Score"));
				stuVo.setModel2Score(rs.getInt("model2Score"));
				stuVo.setModel3Score(rs.getInt("model3Score"));
				stuVo.setModel4Score(rs.getInt("model4Score"));
				stuList.add(stuVo);
			}
			return stuList;
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try {
				dbUtil.closeConnection(con);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return null;
	}
}


